package com.wzcl.app.dao.repository;

import com.wzcl.app.dao.model.WrongTopic;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface WrongTopicRepository extends JpaRepository<WrongTopic, Integer>, JpaSpecificationExecutor<WrongTopic> {

    @Query(value = "SELECT m FROM WrongTopic m WHERE m.userPhone = ?1 AND m.topicAndKnowledgePoint.topic.id = ?2 ")
    List<WrongTopic> findAllByUserPhoneAndTopicId(String userPhone, Integer topicId);

    @Modifying
    @Query(value = "DELETE t1 FROM wrong_topic AS t1 " +
            "INNER JOIN topic_and_knowledge_point AS t2 ON t1.topic_and_knowledge_point_id = t2.id " +
            "WHERE t2.topic_id = ?1 " +
            "AND t1.user_phone = ?2",
            nativeQuery = true)
    void delByTopicIdAndUserPhone(Integer topicId, String userPhone);

    @Modifying
    @Query(value = "DELETE t1 FROM wrong_topic AS t1 " +
            "INNER JOIN topic_and_knowledge_point AS t2 ON t1.topic_and_knowledge_point_id = t2.id " +
            "WHERE t2.topic_id = ?1",
            nativeQuery = true)
    void delByTopicId(Integer topicId);

    @Modifying
    @Query(value = "DELETE t1 FROM wrong_topic AS t1 " +
            "INNER JOIN topic_and_knowledge_point AS t2 ON t1.topic_and_knowledge_point_id = t2.id " +
            "WHERE t2.knowledge_point_id = ?1",
    nativeQuery = true)
    void delByKnowledgePointId(Integer knowledgePointId);

    @Modifying
    @Query(value = "DELETE t1 FROM wrong_topic AS t1 " +
            "WHERE t1.topic_and_knowledge_point_id = ?1",
            nativeQuery = true)
    void delByTopicAndKnowledgePointId(Integer topicAndKnowledgePointId);


    @Query(value = "SELECT " +
            "count(t3.id) as total " +
            "FROM wrong_topic AS t1 " +
            "INNER JOIN topic_and_knowledge_point AS t2 ON t1.topic_and_knowledge_point_id = t2.id " +
            "INNER JOIN knowledge_point AS t3 ON t2.knowledge_point_id = t3.id " +
            "WHERE 1 = 1 " +
            "AND if(?1 IS NULL || ?1 = '',1=1,?1 = t1.user_phone) " +
            "AND if(?2 IS NULL,1=1,?2 = t3.subject_id) " +
            "AND if(?3 IS NULL,1=1,?3 = t3.grade_id) " +
            "GROUP BY t3.id "
            ,nativeQuery = true)
    Integer countCondition(String userPhone, Integer subjectId, Integer gradeId);

    @Query(value = "SELECT " +
            "count(1) as total, " +
            "t3.id as knowledgeId, " +
            "t3.grade_id as gradeId, " +
            "t3.grade_name as gradeName, " +
            "t3.subject_id as subjectId, " +
            "t3.subject_name as subjectName, " +
            "t3.chapter as chapter, " +
            "t3.point as point " +
            "FROM wrong_topic AS t1 " +
            "INNER JOIN topic_and_knowledge_point AS t2 ON t1.topic_and_knowledge_point_id = t2.id " +
            "INNER JOIN knowledge_point AS t3 ON t2.knowledge_point_id = t3.id " +
            "WHERE 1 = 1 " +
            "AND if(?1 IS NULL || ?1 = '',1=1,?1 = t1.user_phone) " +
            "AND if(?2 IS NULL,1=1,?2 = t3.subject_id) " +
            "AND if(?3 IS NULL,1=1,?3 = t3.grade_id) " +
            "GROUP BY t3.id "
            ,nativeQuery = true)
    List<Tuple> findCondition(String userPhone, Integer subjectId, Integer gradeId);


}
